package beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class AutoCompleterBean {
	
	private static final String DB_URL = "jdbc:mysql://localhost:3306/nstweb?user=root&password=root";
	
	private String before = "<ul>";
	private String after = "</ul>";
	
	public String completeCity(String city, String country){
		StringBuffer sb = new StringBuffer();
		
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection(DB_URL);
		    Statement s = c.createStatement();
		    
		    String query = "";
		    
		    if(country.length() > 1 && city.length() >= 1)
		    	query = "SELECT DISTINCT CD.city, C.name FROM overlay CD, countries C WHERE C.name = '"+country+"' AND CD.city LIKE '"+city+"%' AND CD.state = C.country ";
		    if(city.length() >= 1 && country.length() == 0)
		    	query = "SELECT DISTINCT CD.city, C.name FROM overlay CD, countries C WHERE CD.city LIKE '"+city+"%' AND C.country=CD.state ";
		    if(city.length() == 0 && country.length() >=1)
		    	query = "SELECT DISTINCT CD.city, C.name FROM overlay CD, countries C WHERE C.name LIKE '"+country+"%' AND CD.state = C.country ";
		    
		    sb.append(before);
		    ResultSet rs = s.executeQuery(query);
		    while(rs.next())
		    	sb.append("<li id='"+rs.getString("name")+"'>"+rs.getString("city")+"<span class='informal'> ("+rs.getString("name")+")</span></li>");	   
		    
		    sb.append(after);
		    
			s.close();
		    c.close();
		    
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return sb.toString();
		
	}
	
	public String completeCountry(String city, String country){
		
		StringBuffer sb = new StringBuffer();
		
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection(DB_URL);
		    Statement s = c.createStatement();
		    
		    String query = "";
		    
		    if(city.length() > 1 && country.length() >= 1)
		    	query = "SELECT DISTINCT C.name FROM overlay CD, countries C WHERE C.name LIKE '"+country+"%' AND CD.city LIKE '"+city+"%' AND CD.state = C.country ";
		    else if(city.length() > 1 && country.length() == 0)
		    	query = "SELECT DISTINCT C.name FROM overlay CD, countries C WHERE CD.city LIKE '"+city+"%' AND CD.state = C.country ";
		    else if(city.length() == 0 && country.length() >= 1)
		    	query = "SELECT DISTINCT name FROM countries WHERE name LIKE '"+country+"%'";
		    else
		    	return "";
		    sb.append(before);
		    ResultSet rs = s.executeQuery(query);
		    while(rs.next()){
		    	sb.append("<li>"+rs.getString("name")+"</li>");
		    }
		    
		    sb.append(after);
		    
			s.close();
		    c.close();
		    
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return sb.toString();
		
	}
	
	public String getCountryLabel(String countryName){
		String label = "";
		
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection(DB_URL);
		    Statement s = c.createStatement();
		    
		    String query = "SELECT country FROM countries WHERE name='"+countryName+"'";
		    
		    ResultSet rs = s.executeQuery(query);
		    while(rs.next()){
		    	label = rs.getString("country");
		    }
		    
			s.close();
		    c.close();
		    
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return label;
		
	}
	
	
	
	public String getCountryName(String countryLabel){
		String name = "";
		
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection(DB_URL);
		    Statement s = c.createStatement();
		    
		    String query = "SELECT name FROM countries WHERE country='"+countryLabel+"'";
		    
		    ResultSet rs = s.executeQuery(query);
		    while(rs.next()){
		    	name = rs.getString("name");
		    }
		    
			s.close();
		    c.close();
		    
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return name;
		
	}
	
	
	public static void main(String[] args) throws Exception{
		AutoCompleterBean ab = new AutoCompleterBean();
		System.out.println(ab.completeCity("Podol", ""));
	}

}
